package com.example.sqldealtool.dealSqlToWord;

import com.example.sqldealtool.translate.Translation;
import org.apache.ibatis.datasource.pooled.PooledDataSource;
import org.apache.ibatis.mapping.Environment;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.apache.ibatis.transaction.TransactionFactory;
import org.apache.ibatis.transaction.jdbc.JdbcTransactionFactory;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xwpf.usermodel.*;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.*;

/**
 * @author: xjc
 * @date: 2023/4/6 15:55
 **/
public class sqlToWordTool {

    public static void main(String[] args) throws Exception {
        //在getSession修改指定的数据库连接url和密码（任意）
        SqlSessionFactory sqlSessionFactory = getSession();
        SqlSession session = sqlSessionFactory.openSession();
        TableMapper tableMapper = session.getMapper(TableMapper.class);

        //指定数据库名称
        //String schema = "ingestdb";
        String[] schemas = {"migu_ai_server"};
        // 文档生成
        //excelTables(tableMapper, schemas);
        for (String schema : schemas) {
            wordTables(tableMapper, schema);
        }

    }

    private static void excelTables(TableMapper tableMapper, String[] schemas) {
        //保存excel文件
        // 写入数据
        //新建 Excel工作簿对象
        HSSFWorkbook workbook = new HSSFWorkbook();
        //新建工作表
        HSSFSheet sheet = workbook.createSheet("媒资数据库");
        //建立表格的行
        int row = 0;
        HSSFRow rHssfRow = sheet.createRow(row);
        //建立表格的列，这样行列数据有了，就确定了一个单元格，可以写入数据了
        HSSFCell schema_cell = rHssfRow.createCell(0);
        schema_cell.setCellValue("库名");
        HSSFCell table_cell = rHssfRow.createCell(1);
        table_cell.setCellValue("表名");
        HSSFCell table_comment_cell = rHssfRow.createCell(2);
        table_comment_cell.setCellValue("表功能");
        //写入到具体的文件数据中
        FileOutputStream xlsStream = null;
        try {
            for (String schema : schemas) {
                List<TableModel> tableModels = tableMapper.getTables(schema);
                //生成内容
                for (TableModel tableModel : tableModels) {
                    if (StringUtils.isEmpty(tableModel.getTableComment())) {
                        String result = Translation.enToZh(tableModel.getTableName());
                        tableModel.setTableComment(result);
                    }
                    row++;
                    HSSFRow contentRow = sheet.createRow(row);
                    HSSFCell schemaCell = contentRow.createCell(0);
                    schemaCell.setCellValue(schema);
                    HSSFCell tableCell = contentRow.createCell(1);
                    tableCell.setCellValue(tableModel.getTableName());
                    HSSFCell tableCommentCell = contentRow.createCell(2);
                    tableCommentCell.setCellValue(tableModel.getTableComment());
                }
                xlsStream = new FileOutputStream(new File("D:\\download\\poiTest.xls"));
                workbook.write(xlsStream);
            }
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            if (xlsStream != null) {
                try {
                    xlsStream.close();
                } catch (IOException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
    }

    private static void wordTables(TableMapper tableMapper, String schema) {
        try {
            //保存word文件
            File outerFile = new File("D:\\download\\" + schema + ".docx");
            if (outerFile.exists()) {
                outerFile.delete();
            }
            outerFile.createNewFile();
            //word工具
            XWPFDocument xdoc = new XWPFDocument();
            List<TableModel> tableModels = tableMapper.getTables(schema);
            //生成内容
            for (TableModel tableModel : tableModels) {
                List<ColumnModel> columnModels = tableMapper.getColumns(schema, tableModel.getTableName());
                if (CollectionUtils.isEmpty(columnModels)) {
                    continue;
                }
                if (StringUtils.isEmpty(tableModel.getTableComment())) {
                    tableModel.setTableComment(tableModel.getTableName());
                }
                //设置文字，对表格进行描述
                XWPFParagraph paragraph1 = xdoc.createParagraph();
                paragraph1.setAlignment(ParagraphAlignment.LEFT);
                paragraph1.setSpacingBefore(0);
                XWPFRun r1 = paragraph1.createRun();
                r1.setFontFamily("仿宋");
                r1.setStyle("h3");
                r1.setFontSize(14);
                r1.setTextPosition(0);
                r1.setText(tableModel.getTableName() + "（" + tableModel.getTableComment() + "表）");
                XWPFParagraph paragraph2 = xdoc.createParagraph();
                paragraph2.setAlignment(ParagraphAlignment.LEFT);
                paragraph2.setSpacingBefore(0);
                XWPFRun r2 = paragraph2.createRun();
                r2.setFontFamily("仿宋");
                r2.setFontSize(14);
                r2.setTextPosition(0);
                r2.setText("功能：存储" + tableModel.getTableComment() + "数据");
                r2.addCarriageReturn(); // 换行
                r2.setText("表名：" + tableModel.getTableName());
                r2.addCarriageReturn(); // 换行
                r2.setText("主键：id");

                //表格标题
                String[] titles = {
                        "字段",
                        "名称",
                        "类型",
                        "可空",
                        "备注"
                };
                //表格内容
                List<Map<String, String>> values = new ArrayList<>();
                for (ColumnModel model : columnModels) {
                    //按照表格标题格式进行封装
                    if (StringUtils.isEmpty(model.getComment())) {
                        String result = Translation.enToZh(model.getName());
                        model.setComment(result);
                        model.setOtherName(result);
                    }
                    HashMap<String, String> stringStringHashMap = new HashMap<>();
                    stringStringHashMap.put("字段", model.getName());
                    stringStringHashMap.put("名称", model.getOtherName());
                    stringStringHashMap.put("类型", model.getType());
                    stringStringHashMap.put("可空", model.getIsNull());
                    stringStringHashMap.put("备注", model.getComment());
                    values.add(stringStringHashMap);
                }
                WordUtil.createSimpleTable(xdoc, titles, values);
            }
            FileOutputStream fos = new FileOutputStream(outerFile);
            xdoc.write(fos);
            fos.close();
            System.out.println("操作完成！");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    public static SqlSessionFactory getSession() {
        //数据库连接池信息
        PooledDataSource dataSource = new PooledDataSource();
        dataSource.setDriver("com.mysql.cj.jdbc.Driver");
        dataSource.setUsername("root");
        dataSource.setPassword("Arcvideo@01");
        dataSource.setUrl("jdbc:mysql://172.17.81.10:18806/migu_ai_server?useUnicode=yes&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&character_set_server=utf8mb4&useSSL=false&allowPublicKeyRetrieval=true");
        dataSource.setDefaultAutoCommit(false);
        //采用MyBatis的JDBC事务方式
        TransactionFactory transactionFactory = new JdbcTransactionFactory();
        Environment environment = new Environment("development", transactionFactory,
                dataSource);
        //创建 Configuration对象
        Configuration configuration = new Configuration(environment);
        //注册一个MyBatis上下文别名
        configuration.getTypeAliasRegistry().registerAlias("tableMapper", TableMapper.class);
        //加入一个映射器
        configuration.addMapper(TableMapper.class);
        //使用 SqlSessionFactoryBuilder 构建SqlSessionFactory
        SqlSessionFactory SqlSessionFactory =
                new SqlSessionFactoryBuilder().build(configuration);
        return SqlSessionFactory;
    }
}
